{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Project Timecards\n",
    "## Try me\n",
    "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/ffraile/computer_science_tutorials/blob/main/source/Databases/Extra%20Exercises/project%20timecards%20(solved).ipynb)[![Binder](https://mybinder.org/badge_logo.svg)](https://mybinder.org/v2/gh/ffraile/computer_science_tutorials/main?labpath=source%2FDatabases%2FExtra%20Exercises%2Fproject%20timecards%20(solved).ipynb)\n",
    "\n",
    "## Problem definition\n",
    "You work for a startup IT company that performs web development for outside companies. \n",
    "Projects are typically executed by a team of employees, so one or more employees can be assigned to the same project.\n",
    "In any given week, employees might be working on one or more projects. \n",
    "To keep track of what projects, you need to develop a database that keeps in a timecard entity, records of the numbers of hour that each employee worked in each project each week.\n",
    "Draw a database model including entity models for the project, the employees, timecards, and their associations.\n",
    "For every employee, your database needs to store a unique identifier, first and last name, and a contact phone number.\n",
    "For every project, your database needs to store a unique code and the project name. \n",
    "\n",
    "## Solution\n",
    "The following diagram represents a possible solution: \n",
    "\n",
    "![Timecards model](https://github.com/ffraile/database_tutorials/blob/main/Extra%20Exercises/img/timecards.png?raw=true)\n",
    "\n",
    "There is a 1-N relationship between the entities Employee and Timecard, and a 1-N relationship between the entities Timecard and Project.\n",
    " \n",
    "Basically, in this solution, the timecard entity uses as primary key three columns:\n",
    "- employee_fk: This column is a foreign key to the primary key of the employee table (employeeID)\n",
    "- project_fk: This column is a foreign key to the primary key of the projectCode table (projectCode)\n",
    "- weekYear: The week-year, which is the concatenation of the year and the week of the year (e.g. 202132).\n",
    "\n",
    "## Try it yourself\n",
    "The following SQL script creates a database and data model with this solution for you to try:\n",
    "\n",
    "```sql\n",
    "-- Start here if you want to test on your own database\n",
    "DROP DATABASE timecards;\n",
    "CREATE DATABASE timecards;\n",
    "USE timecards;\n",
    "\n",
    "-- Start here if you want to test in sql fidlle\n",
    "CREATE TABLE Employee (\n",
    "\temployeeID INT PRIMARY KEY,\n",
    "    empFirstName VARCHAR(20),\n",
    "    empLastName VARCHAR(20),\n",
    "    empPhone VARCHAR(14)\n",
    ");\n",
    "\n",
    "\n",
    "CREATE TABLE Project (\n",
    "projectCode INT PRIMARY KEY,\n",
    "projectName VARCHAR(100)\n",
    ");\n",
    "\n",
    "CREATE TABLE Timecard (\n",
    "employee_fk INT,\n",
    "project_fk INT,\n",
    "weekYear INT,\n",
    "hours INT,\n",
    "CONSTRAINT timecard_employee_fk FOREIGN KEY (employee_fk) REFERENCES Employee(employeeID),\n",
    "CONSTRAINT timecard_project_fk FOREIGN KEY (project_fk) REFERENCES Project(projectCode),\n",
    "PRIMARY KEY(employee_fk, project_fk, weekYear)\n",
    ");\n",
    "```\n",
    " "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "source": [],
    "metadata": {
     "collapsed": false
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}